package com.estore.daoImpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.estore.converter.DateTime;
import com.estore.dao.Product_Dao;
import com.estore.entity.Brand;
import com.estore.entity.Categories;
import com.estore.entity.MainCategories;
import com.estore.entity.Product;
import com.estore.entity.Product_Image;
//import com.estore.info.CatInfo;
import com.estore.info.ProductImageInfo;
import com.estore.info.ProductInfo;
import com.estore.mapper.BrandMapper;
import com.estore.mapper.CategoriesMapper;
import com.estore.mapper.MainCategoriesMapper;
import com.estore.mapper.ProductImageInfoMapper;
import com.estore.mapper.ProductInfoMapper;
import com.estore.mapper.ProductMapper;
import com.estore.mapper.Product_ImageMapper;

public class Product_DaoImpl implements Product_Dao {

	private DataSource dataSource;
	private JdbcTemplate templateObject;

	@Override
	public void setDataSource(DataSource datasource) {
		this.dataSource = datasource;
		this.templateObject = new JdbcTemplate(datasource);

	}

	@Override
	public Categories getCategories(Integer mainCatID) {
		String sql = "select * from categories where catid = ?";
		try {
			Categories cat = templateObject.queryForObject(sql, new Object[] { mainCatID.toString() }, new CategoriesMapper());
			return cat;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	public void addProduct(ProductInfo info) {
		String sql = "";
		if (info.getContentOfWeb().equals("")) {
			sql = "insert into product (productname, catid, brandid, adddate) value (?, ?, ?, ?)";

			Object[] list = new String[] { info.getProductName(), info.getCatID().toString(), 
					info.getBrandID().toString(), info.getAddDate() };
			templateObject.update(sql, list);
		} else {
			sql = "insert into product (productname, catid, brandid, adddate, contentofweb) value (?, ?, ?, ?, ?)";

			Object[] list = new String[] { info.getProductName(), info.getCatID().toString(), 
					info.getBrandID().toString(), info.getAddDate(), info.getContentOfWeb() };
			templateObject.update(sql, list);
		}
		sql = "select * from product where productname = ?";
		Product product = templateObject.queryForObject(sql,
				new Object[] { info.getProductName() }, new ProductMapper());
		for (String record : info.getListLinkImg()) {
			if (record.equals(info.getListLinkImg().get(0))) {
				sql = "insert into product_image (productid, linkimg, mainimg, imgtype) value (?, ?, 1, 0)";
				templateObject.update(sql, new Object[] { product.getProductID(), record });
			} else {
				sql = "insert into product_image (productid, linkimg, mainimg, imgType) value (?, ?, 0, 0)";
				templateObject.update(sql, new Object[] { product.getProductID(), record });
			}
		}
	}

	@Override
	public Brand getBrand(Integer brandID) {
		String sql = "select * from brand where brandid = ?";
		try {
			Brand brand = templateObject.queryForObject(sql, new Object[] { brandID }, new BrandMapper());
			return brand;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<MainCategories> listMainCategories() {
		String sql = "select * from main_categories";
		try {
			List<MainCategories> categories = templateObject.query(sql, new MainCategoriesMapper());
			return categories;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<Categories> listSubCategories(Integer catID) {
		String sql = "select * from categories where mainCatID = ?";
		try {
			List<Categories> categories = templateObject.query(sql, new Object[] { catID }, new CategoriesMapper());
			return categories;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<Product> getListProduct(Integer catID) {
		String sql = "select * from product where catid = ?";
		try {
			List<Product> product = templateObject.query(sql, new Object[] { catID }, new ProductMapper());
			return product;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<Product> getListAllProduct() {
		String sql = "select * from product";
		try {
			List<Product> product = templateObject.query(sql, new Object[] {}, new ProductMapper());
			return product;
		} catch (Exception e) {
			System.out.println();
			return null;
		}
	}

	@Override
	public void addBrand(String brandName, String website) {
		String sql = "insert into brand (brandName, website) values (?, ?)";
		Object[] list = new String[] { brandName, website };
		templateObject.update(sql, list);

	}

	@Override
	public List<Brand> getListBrand() {
		String sql = "select * from brand";
		List<Brand> brand = templateObject.query(sql, new BrandMapper());
		return brand;
	}

	// check brandName available
	@Override
	public boolean checkBrand(String brandName) {
		String sql = "select * from brand where brandName = ?";
		try {
			templateObject.queryForObject(sql, new Object[] { brandName }, new BrandMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public void updateBrand(Integer brandID, String brandName, String website) {
		String sql = "update brand set brandname = ?, website = ? where brandID = ?";
		templateObject.update(sql, new Object[] { brandName, website, brandID });
	}

	@Override
	public void insertCategories(Categories cat) {
		String sql = "insert into categories (catname, maincatid) values (? , ?)";
		Object[] list = new String[] { cat.getCatName(), cat.getMainCatID().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public boolean checkCat(String catName) {
		String sql = "select * from categories where catName = ?";
		try {
			templateObject.queryForObject(sql, new Object[] { catName }, new CategoriesMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public void updateCategories(Categories info) {
		String sql = "update categories set catName = ?, maincatid = ? where catid = ?";
		Object[] list = new String[] { info.getCatName(), info.getMainCatID().toString(), info.getCatID().toString() };
		templateObject.update(sql, list);
	}

	// check brandName available

	@Override
	public boolean checkProduct(String productName) {
		String sql = "select * from product where productname = ?";
		Object[] list = new String[] { productName };
		try {
			templateObject.queryForObject(sql, list, new ProductMapper());
			return true;
		} catch (Exception e) {

		}
		return false;
	}

	@Override
	public List<ProductInfo> getTop12NewProduct() {
		List<ProductInfo> listProduct;
		DateTime dateTime = new DateTime();
		String currentDate = dateTime.currentDate();
		String sql = "SELECT *" + " FROM product"
				+ " join brand on product.brandid = brand.brandid"
				+ " join categories on categories.catid = product.catid"
				+ " order by addDate desc  LIMIT 12";
		try {
			listProduct = templateObject.query(sql, new ProductInfoMapper());
			return listProduct;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<ProductInfo> getAllProduct(Integer sortType) {
		List<ProductInfo> listProduct;
		String sql = "";
		if (sortType == 1) {
			sql = "SELECT *" + " FROM product"
					+ " join brand on product.brandid = brand.brandid"
					+ " join categories on categories.catid = product.catid"
					+ " order by addDate desc";
			try {
				listProduct = templateObject.query(sql, new ProductInfoMapper());
				return listProduct;
			} catch (Exception e) {
				System.out.println(e);
			}
		}
		return null;
	}

	@Override
	public List<ProductInfo> getProductByCat(Integer catID) {
		List<ProductInfo> listProduct;
		String sql = "SELECT *" + " FROM product"
				+ " join brand on product.brandid = brand.brandid"
				+ " join categories on categories.catid = product.catid"
				+ " where categories.catid = ?";
		Object[] list = new String[] { catID.toString() };
		try {
			listProduct = templateObject.query(sql, list, new ProductInfoMapper());
			return listProduct;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public ProductInfo getProductInfo(Integer ProductID) {
		ProductInfo productInfo;
		String sql = "SELECT *" + " FROM product"
				+ " join brand on product.brandid = brand.brandid"
				+ " join categories on categories.catid = product.catid"
				+ " where product.productid = ?";
		Object[] list = new String[] { ProductID.toString() };
		try {
			productInfo = templateObject.queryForObject(sql, list, new ProductInfoMapper());
			return productInfo;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void editProductDetail(ProductInfo info) {
		String sql = "";
		if (info.getContentOfWeb().equals("")) {
			sql = "update product set productname = ?, brandid = ?, catid =?, contentofweb=? where productid = ?";
			String content = "";
			Object[] list = new String[] { info.getProductName(), info.getBrandID().toString(), 
					info.getCatID().toString(), content, info.getProductID() };
			try {
				templateObject.update(sql, list);
			} catch (Exception e) {
				System.out.println(e);
			}
		} else {
			sql = "update product set productname = ?, brandid = ?, catid =?, contentofweb=? where productid = ?";

			Object[] list = new String[] { info.getProductName(), info.getBrandID().toString(), 
					info.getCatID().toString(), info.getContentOfWeb(), info.getProductID() };
			try {
				templateObject.update(sql, list);
			} catch (Exception e) {
				System.out.println(e);
			}
		}

	}

	@Override
	public boolean checkCurrentProductName(String productName, String productID) {
		String sql = "select * from product where productname = ? and productid = ?";
		Object[] list = new String[] { productName, productID };
		try {
			templateObject.queryForObject(sql, list, new ProductMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public void deleteImage(String linkImg, String productID) {
		String sql = "delete from product_image where linkimg = ?";
		Object[] list = new String[] { linkImg };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println();
		}
	}

	@Override
	public boolean checkMainImg(String productID, String imgType) {
		String sql = "select * from product_image where mainimg =1 and productid =? and imgtype = ?";
		try {
			templateObject.queryForObject(sql, new Object[] { productID, imgType }, new Product_ImageMapper());
			return true;
		} catch (Exception e) {
		}
		return false;
	}

	@Override
	public void setMainImg(String productID, String imgType) {
		String sql = "update product_image set mainimg = 1 where productid = ? and imgtype = ? limit 1";
		try {
			templateObject.update(sql, new Object[] { productID, imgType });
		} catch (Exception e) {

		}
	}

	@Override
	public void addNewImg(List<Product_Image> listProductImg) {
		String sql = "insert into product_image (productid, linkimg, mainimg, imgtype) value (?, ?, ?, ?)";
		for (Product_Image record : listProductImg) {
			Object[] list = new String[] { record.getProductID().toString(), record.getLinkImg(), 
					record.getMainImg().toString(), record.getImgType().toString() };
			templateObject.update(sql, list);
		}
	}

	@Override
	public ProductImageInfo getImageOfProduct(Integer productID, Integer imgType) {
		String sql = "select * from product_image where productid = ? and imgtype = ?";
		Object[] list = new String[] { productID.toString(), imgType.toString() };
		try {
			List<ProductImageInfo> listImg = templateObject.query(sql, list, new ProductImageInfoMapper());
			ProductImageInfo info = new ProductImageInfo();
			List<String> abc = new ArrayList<String>();
			for (ProductImageInfo record : listImg) {
				if (record.getMainImg() == 1) {
					info.setMainImgLink(record.getImgLink());
				}
				abc.add(record.getImgLink());
			}
			info.setListImgLink(abc);
			return info;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<ProductInfo> fullTextSearchResult(String searchText) {
		String sql = "select * from product join categories on product.catid = categories.catid"
				+ " join brand on product.brandid = brand.brandid join main_categories on "
				+ " main_categories.maincatid = categories.maincatid"
				+ " where match (product.productname) against (? in boolean mode) or"
				+ " match (categories.catname) against (? in boolean mode) or"
				+ " match (brand.brandname) against (? in boolean mode) or"
				+ " match (main_categories.maincatname) against (? in boolean mode)";
		List<ProductInfo> listInfo;
		Object[] list = new String[] { searchText, searchText, searchText,
				searchText };
		try {
			listInfo = templateObject.query(sql, list, new ProductInfoMapper());
			return listInfo;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<ProductInfo> exactlySearchResult(String searchText) {
		String sql = "select * from product join categories on product.catid = categories.catid " +
				" join brand on product.brandid = brand.brandid join main_categories on main_categories.maincatid = categories.maincatid where"
				+ " concat(categories.catname, ' ', product.productname,' ', brand.brandname, ' ', main_categories.maincatname) like ? or "
				+ " concat(categories.catname, ' ', product.productname, ' ', main_categories.maincatname, ' ', brand.brandname) like ? or "
				+ " concat(categories.catname, ' ', main_categories.maincatname, ' ' , product.productname,' ', brand.brandname) like ? or "
				+ " concat(categories.catname, ' ', main_categories.maincatname, ' ' , brand.brandname,' ', product.productname) like ? or "
				+ " concat(categories.catname, ' ', brand.brandname, ' ' , product.productname,' ', main_categories.maincatname) like ? or "
				+ " concat(categories.catname, ' ', brand.brandname, ' ' , main_categories.maincatname,' ', product.productname) like ? or "
				+ " concat(brand.brandname, ' ', categories.catname, ' ' , product.productname,' ', main_categories.maincatname) like ? or "
				+ " concat(brand.brandname, ' ', categories.catname, ' ' , main_categories.maincatname,' ', product.productname) like ? or "
				+ " concat(brand.brandname, ' ', product.productname, ' ' , categories.catname,' ', main_categories.maincatname) like ? or "
				+ " concat(brand.brandname, ' ', product.productname, ' ' , main_categories.maincatname,' ', categories.catname) like ? or "
				+ " concat(brand.brandname, ' ', main_categories.maincatname, ' ' , categories.catname,' ', product.productname) like ? or "
				+ " concat(brand.brandname, ' ', main_categories.maincatname, ' ' , product.productname,' ', categories.catname) like ? or "
				+ " concat(main_categories.maincatname, ' ', brand.brandname, ' ' , categories.catname,' ', product.productname) like ? or "
				+ " concat(main_categories.maincatname, ' ', brand.brandname, ' ' , product.productname,' ', categories.catname) like ? or "
				+ " concat(main_categories.maincatname, ' ', categories.catname, ' ' , brand.brandname,' ', product.productname) like ? or "
				+ " concat(main_categories.maincatname, ' ', categories.catname, ' ' , product.productname,' ', brand.brandname) like ? or "
				+ " concat(main_categories.maincatname, ' ', product.productname, ' ' , brand.brandname,' ', categories.catname) like ? or "
				+ " concat(main_categories.maincatname, ' ', product.productname, ' ' , categories.catname,' ', brand.brandname) like ? or "
				+ " concat(product.productname, ' ', main_categories.maincatname, ' ' , brand.brandname,' ', categories.catname) like ? or "
				+ " concat(product.productname, ' ', main_categories.maincatname, ' ' , categories.catname,' ', brand.brandname) like ? or "
				+ " concat(product.productname, ' ', brand.brandname, ' ' , main_categories.maincatname,' ', categories.catname) like ? or "
				+ " concat(product.productname, ' ', brand.brandname, ' ' , categories.catname,' ', main_categories.maincatname) like ? or "
				+ " concat(product.productname, ' ', categories.catname, ' ' , main_categories.maincatname,' ', brand.brandname) like ? or "
				+ " concat(product.productname, ' ', categories.catname, ' ' , brand.brandname,' ', main_categories.maincatname) like ?";
		Object[] list = new String[] { searchText, searchText, searchText,
				searchText, searchText, searchText, searchText, searchText, 
				searchText, searchText, searchText, searchText, searchText, 
				searchText, searchText, searchText, searchText, searchText, 
				searchText, searchText, searchText, searchText, searchText, searchText };
		List<ProductInfo> listProduct;
		try {
			listProduct = templateObject.query(sql, list, new ProductInfoMapper());
			return listProduct;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void addMainCategories(MainCategories mainCategories) {
		String sql = "insert into main_categories (maincatname) value (?)";
		Object[] list = new String[] { mainCategories.getMainCatName() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public boolean checkMainCategories(MainCategories main) {
		String sql = "select * from main_categories where maincatname = ?";
		Object[] list = new String[] { main.getMainCatName() };
		try {
			templateObject.queryForObject(sql, list, new MainCategoriesMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public List<MainCategories> getAllMainCategories() {
		String sql = "select * from main_categories";
		try {
			List<MainCategories> listCat = templateObject.query(sql, new MainCategoriesMapper());
			return listCat;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public MainCategories viewMainCategories(Integer mainCatID) {
		String sql = "select * from main_categories where mainCatID = ?";
		Object[] list = new String[] { mainCatID.toString() };
		try {
			MainCategories cat = templateObject.queryForObject(sql, list, new MainCategoriesMapper());
			return cat;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void editMainCategories(MainCategories mainCategories) {
		String sql = "update main_categories set maincatname = ? where maincatid = ?";
		Object[] list = new String[] { mainCategories.getMainCatName(), mainCategories.getMainCatID().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public boolean checkCurrentCatName(Categories cat) {
		String sql = "select * from categories where catname = ? and catid = ?";
		Object[] list = new String[] { cat.getCatName(), cat.getCatID().toString() };
		try {
			templateObject.queryForObject(sql, list, new CategoriesMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public List<Categories> listAllCategories() {
		String sql = "select * from categories";
		try {
			List<Categories> listCat = templateObject.query(sql, new CategoriesMapper());
			return listCat;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<Integer> get12MostFavoriteProductID() {
		String sql = "select * from product join favoriteproduct on product.productID = favoriteproduct.productid" +
				" group by favoriteproduct.productID order by count(favoriteproduct.productID) desc limit 12";
		List<Integer> listProductID = new ArrayList<Integer>();
		try {
			List<Product> listProduct = templateObject.query(sql, new ProductMapper());
			for(Product record : listProduct){
				listProductID.add(record.getProductID());
			}
			return listProductID;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public Integer getFavoriteCount(Integer productID) {
		String sql = "select productid from favoriteproduct where productid = ?";
		Object[] list = new String[] { productID.toString() };
		try {
			List<Integer> listCount = templateObject.query(sql, list, new RowMapper<Integer>() {
					@Override
					public Integer mapRow(ResultSet rs, int arg1) throws SQLException {
						Integer productID = rs.getInt("productID");
						return productID;
					}
				});
			Integer count = 0;
			for(Integer record : listCount){
				count ++ ;
			}
			return count;
		} catch (Exception e) {
			// TODO: handle exception
		}
		return null;
	}

}
